﻿using System;
using System.Data;
using System.IO;
using NPOI.SS.UserModel;
using NPOI.XSSF.UserModel;
using NPOI.HSSF.UserModel;

namespace LongGi.TingkatKebocoranModule
{
   

    //public class ExcelExporter
    //{
    //    /// <summary>
    //    /// 将 DataTable 导出为 Excel 文件
    //    /// </summary>
    //    /// <param name="dataTable">数据表</param>
    //    /// <param name="filePath">文件路径</param>
    //    /// <param name="sheetName">工作表名称</param>
    //    /// <param name="isXlsx">是否导出为 xlsx 格式 (默认true)，false 则导出为 xls</param>
    //    public static void ExportToExcel(DataTable dataTable, string filePath, string sheetName = "Sheet1", bool isXlsx = true)
    //    {
    //        if (dataTable == null || dataTable.Rows.Count == 0)
    //            throw new ArgumentException("DataTable 不能为空");

    //        IWorkbook workbook;

    //        // 根据格式创建 Workbook
    //        if (isXlsx)
    //            workbook = new XSSFWorkbook();
    //        else
    //            workbook = new HSSFWorkbook();

    //        // 创建工作表
    //        ISheet sheet = workbook.CreateSheet(sheetName);

    //        // 创建表头样式
    //        ICellStyle headerStyle = workbook.CreateCellStyle();
    //        headerStyle.FillForegroundColor = IndexedColors.LightBlue.Index;
    //        headerStyle.FillPattern = FillPattern.SolidForeground;
    //        headerStyle.BorderBottom = BorderStyle.Thin;
    //        headerStyle.BorderLeft = BorderStyle.Thin;
    //        headerStyle.BorderRight = BorderStyle.Thin;
    //        headerStyle.BorderTop = BorderStyle.Thin;

    //        IFont headerFont = workbook.CreateFont();
    //        headerFont.IsBold = true;
    //        headerStyle.SetFont(headerFont);

    //        // 创建数据行样式
    //        ICellStyle dataStyle = workbook.CreateCellStyle();
    //        dataStyle.BorderBottom = BorderStyle.Thin;
    //        dataStyle.BorderLeft = BorderStyle.Thin;
    //        dataStyle.BorderRight = BorderStyle.Thin;
    //        dataStyle.BorderTop = BorderStyle.Thin;

    //        // 创建表头行
    //        IRow headerRow = sheet.CreateRow(0);
    //        for (int i = 0; i < dataTable.Columns.Count; i++)
    //        {
    //            ICell cell = headerRow.CreateCell(i);
    //            cell.SetCellValue(dataTable.Columns[i].ColumnName);
    //            cell.CellStyle = headerStyle;
             
    //            // 自动调整列宽
    //            sheet.AutoSizeColumn(i);
    //        }

    //        // 填充数据
    //        for (int i = 0; i < dataTable.Rows.Count; i++)
    //        {
    //            IRow dataRow = sheet.CreateRow(i + 1);
    //            for (int j = 0; j < dataTable.Columns.Count; j++)
    //            {
    //                ICell cell = dataRow.CreateCell(j);
    //                object value = dataTable.Rows[i][j];

    //                // 根据数据类型设置单元格值
    //                if (value != null && value != DBNull.Value)
    //                {
    //                    SetCellValue(cell, value);
    //                }
    //                else
    //                {
    //                    cell.SetCellValue("");
    //                }

    //                cell.CellStyle = dataStyle;
    //            }
    //        }

    //        // 保存文件
    //        using (FileStream fs = new FileStream(filePath, FileMode.Create, FileAccess.Write))
    //        {
    //            workbook.Write(fs);
    //        }
    //    }

    //    /// <summary>
    //    /// 根据数据类型设置单元格值
    //    /// </summary>
    //    private static void SetCellValue(ICell cell, object value)
    //    {
    //        switch (value)
    //        {
    //            case int intValue:
    //                cell.SetCellValue(intValue);
    //                break;
    //            case long longValue:
    //                cell.SetCellValue(longValue);
    //                break;
    //            case double doubleValue:
    //                cell.SetCellValue(doubleValue);
    //                break;
    //            case decimal decimalValue:
    //                cell.SetCellValue((double)decimalValue);
    //                break;
    //            case float floatValue:
    //                cell.SetCellValue(floatValue);
    //                break;
    //            case bool boolValue:
    //                cell.SetCellValue(boolValue);
    //                break;
    //            case DateTime dateValue:
    //                cell.SetCellValue(dateValue.ToString("yyyy-MM-dd HH:mm"));
    //                break;
    //            default:
    //                cell.SetCellValue(value.ToString());
    //                break;
    //        }
    //    }

    //    /// <summary>
    //    /// 导出为内存流
    //    /// </summary>
    //    public static MemoryStream ExportToStream(DataTable dataTable, string sheetName = "Sheet1", bool isXlsx = true)
    //    {
    //        if (dataTable == null || dataTable.Rows.Count == 0)
    //            throw new ArgumentException("DataTable 不能为空");

    //        IWorkbook workbook = isXlsx ? new XSSFWorkbook() : new HSSFWorkbook();
    //        ISheet sheet = workbook.CreateSheet(sheetName);

    //        // 创建表头
    //        IRow headerRow = sheet.CreateRow(0);
    //        for (int i = 0; i < dataTable.Columns.Count; i++)
    //        {
    //            headerRow.CreateCell(i).SetCellValue(dataTable.Columns[i].ColumnName);
    //            sheet.AutoSizeColumn(i);
    //        }

    //        // 填充数据
    //        for (int i = 0; i < dataTable.Rows.Count; i++)
    //        {
    //            IRow dataRow = sheet.CreateRow(i + 1);
    //            for (int j = 0; j < dataTable.Columns.Count; j++)
    //            {
    //                object value = dataTable.Rows[i][j];
    //                dataRow.CreateCell(j).SetCellValue(value?.ToString() ?? "");
    //            }
    //        }

    //        MemoryStream stream = new MemoryStream();
    //        workbook.Write(stream, true);
    //        stream.Position = 0;

    //        return stream;
    //    }
    //}











public class ExcelExporter
    {
        /// <summary>
        /// Excel 导出配置
        /// </summary>
        public class ExportConfig
        {
            public int MinColumnWidth { get; set; } = 8;    // 最小列宽（字符数）
            public int MaxColumnWidth { get; set; } = 60;   // 最大列宽（字符数）
            public int DefaultColumnWidth { get; set; } = 15; // 默认列宽（字符数）
            public bool AutoFitColumns { get; set; } = true; // 是否自动调整列宽
            public bool UseChineseWidthOptimization { get; set; } = true; // 是否优化中文字符宽度
            public bool FreezeHeader { get; set; } = true; // 是否冻结表头
        }

        /// <summary>
        /// 将 DataTable 导出为 Excel 文件
        /// </summary>
        public static void ExportToExcel(DataTable dataTable, string filePath,
            string sheetName = "Sheet1", bool isXlsx = true, ExportConfig config = null)
        {
            if (dataTable == null || dataTable.Rows.Count == 0)
                throw new ArgumentException("DataTable 不能为空");

            config = config ?? new ExportConfig();

            IWorkbook workbook = isXlsx ? new XSSFWorkbook() : new HSSFWorkbook();
            ISheet sheet = workbook.CreateSheet(sheetName);

            // 创建样式
            ICellStyle headerStyle = CreateHeaderStyle(workbook);
            ICellStyle dataStyle = CreateDataStyle(workbook);

            // 创建表头
            CreateHeaderRow(sheet, dataTable, headerStyle);

            // 填充数据
            FillDataRows(sheet, dataTable, dataStyle);

            // 优化列宽
            if (config.AutoFitColumns)
            {
                OptimizeColumnWidth(sheet, dataTable, config);
            }
            else
            {
                SetUniformColumnWidth(sheet, dataTable, config.DefaultColumnWidth);
            }

            // 冻结表头
            if (config.FreezeHeader)
            {
                sheet.CreateFreezePane(0, 1, 0, 1);
            }

            // 保存文件
            using (FileStream fs = new FileStream(filePath, FileMode.Create, FileAccess.Write))
            {
                workbook.Write(fs);
            }
        }

        /// <summary>
        /// 创建表头行
        /// </summary>
        private static void CreateHeaderRow(ISheet sheet, DataTable dataTable, ICellStyle headerStyle)
        {
            IRow headerRow = sheet.CreateRow(0);
            for (int i = 0; i < dataTable.Columns.Count; i++)
            {
                ICell cell = headerRow.CreateCell(i);
                cell.SetCellValue(dataTable.Columns[i].ColumnName);
                cell.CellStyle = headerStyle;
            }
        }

        /// <summary>
        /// 填充数据行
        /// </summary>
        private static void FillDataRows(ISheet sheet, DataTable dataTable, ICellStyle dataStyle)
        {
            for (int i = 0; i < dataTable.Rows.Count; i++)
            {
                IRow dataRow = sheet.CreateRow(i + 1);
                for (int j = 0; j < dataTable.Columns.Count; j++)
                {
                    ICell cell = dataRow.CreateCell(j);
                    object value = dataTable.Rows[i][j];
                    SetCellValue(cell, value, dataTable.Columns[j].DataType);
                    cell.CellStyle = dataStyle;
                }
            }
        }

        /// <summary>
        /// 优化列宽设置
        /// </summary>
        private static void OptimizeColumnWidth(ISheet sheet, DataTable dataTable, ExportConfig config)
        {
            for (int i = 0; i < dataTable.Columns.Count; i++)
            {
                int maxContentWidth = GetMaxContentWidth(dataTable, i, config.UseChineseWidthOptimization);

                // 应用宽度限制
                int finalWidth = Math.Max(config.MinColumnWidth, Math.Min(maxContentWidth, config.MaxColumnWidth));

                // 设置列宽（NPOI中1个字符宽度约为256）
                sheet.SetColumnWidth(i, finalWidth * 256);
            }
        }

        /// <summary>
        /// 获取列的最大内容宽度
        /// </summary>
        private static int GetMaxContentWidth(DataTable dataTable, int columnIndex, bool useChineseOptimization)
        {
            int maxWidth = 0;
            string columnName = dataTable.Columns[columnIndex].ColumnName;

            // 考虑表头宽度
            maxWidth = CalculateTextWidth(columnName, useChineseOptimization);

            // 考虑数据行宽度
            foreach (DataRow row in dataTable.Rows)
            {
                if (row[columnIndex] != null && row[columnIndex] != DBNull.Value)
                {
                    string cellValue = row[columnIndex].ToString();
                    int cellWidth = CalculateTextWidth(cellValue, useChineseOptimization);
                    if (cellWidth > maxWidth)
                    {
                        maxWidth = cellWidth;
                    }
                }
            }

            return maxWidth + 2; // 添加边距
        }

        /// <summary>
        /// 计算文本宽度（考虑中英文字符差异）
        /// </summary>
        private static int CalculateTextWidth(string text, bool useChineseOptimization)
        {
            if (string.IsNullOrEmpty(text)) return 0;

            int width = 0;
            foreach (char c in text)
            {
                if (useChineseOptimization && IsChineseCharacter(c))
                {
                    width += 2; // 中文字符算2个宽度
                }
                else
                {
                    width += 1; // 英文字符算1个宽度
                }
            }

            return width;
        }

        /// <summary>
        /// 判断是否为中文字符
        /// </summary>
        private static bool IsChineseCharacter(char c)
        {
            return (c >= 0x4E00 && c <= 0x9FFF) || // CJK统一表意文字
                   (c >= 0x3400 && c <= 0x4DBF) || // CJK扩展A
                   (c >= 0x20000 && c <= 0x2A6DF); // CJK扩展B
        }

        /// <summary>
        /// 设置统一列宽
        /// </summary>
        private static void SetUniformColumnWidth(ISheet sheet, DataTable dataTable, int columnWidth)
        {
            for (int i = 0; i < dataTable.Columns.Count; i++)
            {
                sheet.SetColumnWidth(i, columnWidth * 256);
            }
        }

        /// <summary>
        /// 创建表头样式
        /// </summary>
        private static ICellStyle CreateHeaderStyle(IWorkbook workbook)
        {
            ICellStyle style = workbook.CreateCellStyle();

            // 背景色
            style.FillForegroundColor = IndexedColors.LightBlue.Index;
            style.FillPattern = FillPattern.SolidForeground;

            // 边框
            style.BorderBottom = BorderStyle.Thin;
            style.BorderLeft = BorderStyle.Thin;
            style.BorderRight = BorderStyle.Thin;
            style.BorderTop = BorderStyle.Thin;

            // 字体
            IFont font = workbook.CreateFont();
            font.IsBold = true;
            font.FontHeightInPoints = 11;
            style.SetFont(font);

            // 对齐方式
            style.Alignment = HorizontalAlignment.Center;
            style.VerticalAlignment = VerticalAlignment.Center;

            return style;
        }

        /// <summary>
        /// 创建数据行样式
        /// </summary>
        private static ICellStyle CreateDataStyle(IWorkbook workbook)
        {
            ICellStyle style = workbook.CreateCellStyle();

            // 边框
            style.BorderBottom = BorderStyle.Thin;
            style.BorderLeft = BorderStyle.Thin;
            style.BorderRight = BorderStyle.Thin;
            style.BorderTop = BorderStyle.Thin;

            // 垂直居中
            style.VerticalAlignment = VerticalAlignment.Center;

            return style;
        }

        /// <summary>
        /// 设置单元格值
        /// </summary>
        private static void SetCellValue(ICell cell, object value, Type dataType)
        {
            if (value == null || value == DBNull.Value)
            {
                cell.SetCellValue("");
                return;
            }

            switch (value)
            {
                case int intValue:
                    cell.SetCellValue(intValue);
                    break;
                case long longValue:
                    cell.SetCellValue(longValue);
                    break;
                case double doubleValue:
                    cell.SetCellValue(doubleValue);
                    break;
                case decimal decimalValue:
                    cell.SetCellValue((double)decimalValue);
                    break;
                case float floatValue:
                    cell.SetCellValue(floatValue);
                    break;
                case bool boolValue:
                    cell.SetCellValue(boolValue);
                    break;
                case DateTime dateValue:
                    cell.SetCellValue(dateValue.ToString("yyyy-mm-dd HH:mm"));
                    break;
                default:
                    cell.SetCellValue(value.ToString());
                    break;
            }
        }

        /// <summary>
        /// 导出为内存流
        /// </summary>
        public static MemoryStream ExportToStream(DataTable dataTable, string sheetName = "Sheet1",
            bool isXlsx = true, ExportConfig config = null)
        {
            if (dataTable == null || dataTable.Rows.Count == 0)
                throw new ArgumentException("DataTable 不能为空");

            config = config ?? new ExportConfig();

            IWorkbook workbook = isXlsx ? new XSSFWorkbook() : new HSSFWorkbook();
            ISheet sheet = workbook.CreateSheet(sheetName);

            // 创建表头
            IRow headerRow = sheet.CreateRow(0);
            for (int i = 0; i < dataTable.Columns.Count; i++)
            {
                headerRow.CreateCell(i).SetCellValue(dataTable.Columns[i].ColumnName);
            }

            // 填充数据
            for (int i = 0; i < dataTable.Rows.Count; i++)
            {
                IRow dataRow = sheet.CreateRow(i + 1);
                for (int j = 0; j < dataTable.Columns.Count; j++)
                {
                    object value = dataTable.Rows[i][j];
                    dataRow.CreateCell(j).SetCellValue(value?.ToString() ?? "");
                }
            }

            // 优化列宽
            if (config.AutoFitColumns)
            {
                OptimizeColumnWidth(sheet, dataTable, config);
            }
            else
            {
                SetUniformColumnWidth(sheet, dataTable, config.DefaultColumnWidth);
            }

            MemoryStream stream = new MemoryStream();
            workbook.Write(stream, true);
            stream.Position = 0;

            return stream;
        }
    }








}
